# Save Report Controller Documentation

**File**: `/controllers/saveReportController.php`  
**Purpose**: Generates financial reports for cash registers showing sales performance and expense breakdown  
**Last Updated**: December 20, 2024  
**Total Functions**: 2  
**Lines of Code**: ~100

---

## 📋 Overview

The Save Report Controller is a lightweight reporting module that provides essential financial analytics for cash register operations. It focuses on revenue and expense analysis for specific cash registers within defined time periods, helping businesses understand the financial performance of individual sales points.

### Primary Functions
- [x] Display cash register selection interface
- [x] Generate sales and expense reports by cash register
- [x] Date range filtering for time-based analysis
- [x] Sales revenue aggregation across multiple bill types
- [x] Expense categorization and summation
- [x] User permission-based cash register access

### Related Controllers
- [saveController.php](#) - Cash register management
- [sellbillController.php](#) - Sales transactions
- [returnsellbillController.php](#) - Sales returns
- [expensesController.php](#) - Expense management

---

## 🗄️ Database Tables

### Primary Tables (Queried via RedBeanPHP)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **save** | Cash registers/safes | saveid, savename, savecurrentvalue |
| **sellbill** | Sales bills | sellbillid, sellbilltotalpayed, sellbillsaveid, sellbilldate, conditions |
| **returnsellbill** | Return bills | returnsellbillid, returnsellbilltotalpayed, returnsellbillsaveid, returnsellbilldate, conditions |
| **sellbillandrutern** | Combined bills | sellbillid, sellbilltotalpayed, sellbillsaveid, sellbilldate, conditions |
| **expenses** | Expense transactions | expensesid, expensesValue, expensestypeid, saveid, expensesdate, conditions |
| **expensestype** | Expense categories | expensestypeid, expensestypename |

---

## 🔑 Key Functions

### 1. **Default Action** - Display Report Interface
**Location**: Lines 9-27  
**Purpose**: Show cash register selection form for report generation

**Process Flow**:
1. **Permission Check**: Determine user's cash register access level
2. **Load Available Saves**: Get accessible cash registers based on user permissions
3. **Display Form**: Show save selection interface

**User Permission Logic**:
```php
if ($_SESSION['searchinonesave'] == 0) {
    if ($_SESSION['saveids'] == 0) {
        $saveData = $mySaveEx->queryAllEX2($queryString); // All saves
    } else {
        $queryString .= ' and saveid in (' . $_SESSION['saveids'] . ')';
        $saveData = $mySaveEx->queryAllEX2($queryString); // Filtered saves
    }
} else {
    $queryString .= ' and saveid = ' . $_SESSION['saveid'] . ' ';
    $saveData = $mySaveEx->queryAllEX2($queryString); // Single save
}
```

**Template**: `savereportview/show.html`

---

### 2. **show()** - Generate Cash Register Report
**Location**: Lines 28-99  
**Purpose**: Generate comprehensive sales and expense report for selected cash register

**Function Signature**:
```php
// POST Parameters
$from = filter_input(INPUT_POST, 'fromdate');  // Start date
$to = filter_input(INPUT_POST, 'todate');      // End date  
$saveid = filter_input(INPUT_POST, 'saveid');   // Cash register ID
```

**Process Flow**:
1. **Validation**: Ensure cash register is selected
2. **Date Range Setup**: Process from/to dates with time boundaries
3. **Sales Data Aggregation**: Sum revenue from multiple bill types
4. **Expense Data Collection**: Group expenses by category
5. **Report Generation**: Display results with totals

**Sales Calculation Logic**:
```php
// Build query filters for each table
$sellbillqs = ' and sellbill.sellbillsaveid = ' . $saveid;
$retsellbillqs = ' and returnsellbill.returnsellbillsaveid = ' . $saveid;
$sellbillretqs = ' and sellbillandrutern.sellbillsaveid = ' . $saveid;

// Apply date filters
if ($from) {
    $from = $from . " 00:00:00";
    $sellbillqs .= ' and sellbill.sellbilldate >= "' . $from . '"';
    $retsellbillqs .= ' and returnsellbill.returnsellbilldate >= "' . $from . '"';
    $sellbillretqs .= ' and sellbillandrutern.sellbilldate >= "' . $from . '"';
}

if ($to) {
    $to = $to . ' 23:59:59';
    $sellbillqs .= ' and sellbill.sellbilldate <= "' . $to . '"';
    $retsellbillqs .= ' and returnsellbill.returnsellbilldate <= "' . $to . '"';
    $sellbillretqs .= ' and sellbillandrutern.sellbilldate <= "' . $to . '"';
}

// Aggregate sales data
$allSellBills = R::getCell('select SUM(sellbilltotalpayed) FROM sellbill where conditions = 0 ' . $sellbillqs);
$allRetSellBills = R::getCell('select SUM(returnsellbilltotalpayed) FROM returnsellbill where conditions = 0 ' . $retsellbillqs);
$allSellRetBills = R::getCell('select SUM(sellbilltotalpayed) FROM sellbillandrutern where conditions = 0 ' . $sellbillretqs);

// Calculate net sales
$totalSells = (float) ($allSellBills + $allSellRetBills - abs($allRetSellBills));
```

**Expense Aggregation**:
```php
$expensesqs = ' and expenses.saveid = ' . $saveid;

// Apply date filters for expenses
if ($from) {
    $expensesqs .= ' and expenses.expensesdate >= "' . $from . '"';
}
if ($to) {
    $expensesqs .= ' and expenses.expensesdate <= "' . $to . '"';
}

// Group expenses by type
$allExpenses = R::getAll('select expensestype.expensestypename, SUM(expensesValue) totalEx 
                         from expenses 
                         join expensestype ON expensestype.expensestypeid = expenses.expensestypeid 
                         where expenses.conditions = 0 ' . $expensesqs . ' 
                         group by expenses.expensestypeid');
```

---

## 🔄 Workflows

### Workflow 1: Generate Cash Register Report
```
┌─────────────────────────────────────────────────────────────┐
│            START: Generate Save Report                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Select Cash Register                                    │
│     - Choose from available saves                           │
│     - Set date range (optional)                             │
│     - Submit report request                                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Validate Input                                          │
│     - Check save ID is provided                             │
│     - Verify user has access to selected save              │
│     - Process date range with time boundaries              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Aggregate Sales Data                                    │
│     - Query sellbill table for regular sales               │
│     - Query returnsellbill for returns (subtract)          │
│     - Query sellbillandrutern for combined bills           │
│     - Apply date and save filters                          │
│     - Calculate net sales total                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Aggregate Expense Data                                  │
│     - Query expenses table with save filter                │
│     - Join with expensestype for category names            │
│     - Group by expense type                                │
│     - Sum total expenses per category                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Generate Report                                         │
│     - Display sales summary                                 │
│     - Show expense breakdown by category                    │
│     - Include date range and save name                     │
│     - Calculate net profit (sales - expenses)              │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| (empty) | Default action | Display report form |
| `do=show` | `show()` | Generate and display report |

### Required Parameters by Action

**Display Form** (empty `do`):
- No parameters required
- User permissions determine available saves

**Generate Report** (`do=show`):
- `saveid` - Cash register ID (required)
- `fromdate` - Start date (YYYY-MM-DD) [optional]
- `todate` - End date (YYYY-MM-DD) [optional]

---

## 🧮 Calculation Methods

### Net Sales Calculation
```php
// Formula: Regular Sales + Combined Bills - Returns
$totalSells = (float) ($allSellBills + $allSellRetBills - abs($allRetSellBills));
```

**Components**:
- `$allSellBills`: Sum of `sellbilltotalpayed` from `sellbill` table
- `$allSellRetBills`: Sum of `sellbilltotalpayed` from `sellbillandrutern` table  
- `$allRetSellBills`: Sum of `returnsellbilltotalpayed` from `returnsellbill` table (subtracted)

### Expense Aggregation
```php
// Group by expense type and sum values
SELECT expensestype.expensestypename, SUM(expensesValue) as totalEx
FROM expenses 
JOIN expensestype ON expensestype.expensestypeid = expenses.expensestypeid 
WHERE expenses.conditions = 0 AND expenses.saveid = [SAVE_ID]
GROUP BY expenses.expensestypeid
```

### Date Range Processing
```php
// Add time boundaries for accurate filtering
if ($from) {
    $from = $from . " 00:00:00";  // Start of day
}
if ($to) {
    $to = $to . ' 23:59:59';      // End of day
}
```

---

## 🔒 Security & Permissions

### User-Based Save Access
```php
// Multi-save access
if ($_SESSION['searchinonesave'] == 0) {
    if ($_SESSION['saveids'] == 0) {
        // User can see all saves
    } else {
        // User limited to specific saves
        $queryString .= ' and saveid in (' . $_SESSION['saveids'] . ')';
    }
} else {
    // User limited to single save
    $queryString .= ' and saveid = ' . $_SESSION['saveid'] . ' ';
}
```

### Input Validation
```php
$from = filter_input(INPUT_POST, 'fromdate');
$to = filter_input(INPUT_POST, 'todate');
$saveid = filter_input(INPUT_POST, 'saveid');
```

**Validation Rules**:
- Save ID must be provided for report generation
- Date formats validated by filter_input
- User must have access to selected save

### Data Integrity
- Only active transactions included (`conditions = 0`)
- Proper JOIN relationships maintained
- Date boundaries correctly applied

---

## 📊 Performance Considerations

### Database Optimization
1. **Indexes Recommended**:
   - `sellbill(sellbillsaveid, sellbilldate, conditions)`
   - `returnsellbill(returnsellbillsaveid, returnsellbilldate, conditions)`
   - `sellbillandrutern(sellbillsaveid, sellbilldate, conditions)`
   - `expenses(saveid, expensesdate, conditions)`
   - `expensestype(expensestypeid)`

2. **Query Efficiency**:
   - Use of aggregate functions (SUM)
   - Proper WHERE clause filtering
   - Efficient JOIN operations

### Memory Management
- Minimal data loading for dropdowns
- Direct SQL aggregation vs. PHP processing
- Clean result set handling

### Known Performance Issues
```php
// Potential issue: Multiple separate queries instead of JOINs
$allSellBills = R::getCell('select SUM(sellbilltotalpayed) FROM sellbill...');
$allRetSellBills = R::getCell('select SUM(returnsellbilltotalpayed) FROM returnsellbill...');
$allSellRetBills = R::getCell('select SUM(sellbilltotalpayed) FROM sellbillandrutern...');

// Consider: Single query with UNIONs for better performance
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Report Data**
**Issue**: Report shows zero sales/expenses despite known transactions  
**Cause**: Incorrect save ID or date range filters

**Debug**:
```sql
-- Check if save has transactions
SELECT COUNT(*) FROM sellbill WHERE sellbillsaveid = [SAVE_ID] AND conditions = 0;

-- Check date format
SELECT sellbilldate FROM sellbill WHERE sellbillsaveid = [SAVE_ID] LIMIT 5;
```

### 2. **Incorrect Sales Totals**
**Issue**: Sales total doesn't match expected amount  
**Cause**: Returns not properly subtracted or wrong bill types included

**Debug**:
```php
echo "Regular Sales: $allSellBills<br>";
echo "Combined Bills: $allSellRetBills<br>";  
echo "Returns: $allRetSellBills<br>";
echo "Net Total: $totalSells<br>";
```

### 3. **Missing Expense Categories**
**Issue**: Some expenses not appearing in report  
**Cause**: Missing JOIN with expense types table

**Fix**: Ensure all expenses have valid `expensestypeid`

### 4. **Permission Errors**
**Issue**: User cannot see certain cash registers  
**Cause**: Save permission configuration

**Debug**:
```php
echo "Search in One Save: " . $_SESSION['searchinonesave'] . "<br>";
echo "User Save IDs: " . $_SESSION['saveids'] . "<br>";
echo "User Save ID: " . $_SESSION['saveid'] . "<br>";
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Report Generation
```
1. Login with user having save access
2. Select cash register with known transactions
3. Generate report without date filter
4. Verify sales and expense totals
5. Check expense breakdown by category
```

### Test Case 2: Date Range Filtering
```
1. Select cash register with historical data
2. Set specific date range
3. Generate report
4. Verify only transactions in range included
5. Compare with manual calculation
```

### Test Case 3: Multiple Save Access
```
1. Login with user having multiple save access
2. Verify dropdown shows authorized saves only
3. Test report generation for different saves
4. Confirm data isolation between saves
```

### Test Case 4: Empty Result Handling
```
1. Select save with no transactions
2. Generate report
3. Verify graceful handling of zero results
4. Check no errors displayed
```

### Test Case 5: Permission Restrictions
```
1. Login with restricted save access
2. Verify only authorized saves visible
3. Attempt to generate report
4. Check permission enforcement
```

### Debug Mode Enable
```php
// Add debugging at top of file
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug queries
echo "Sales Query: " . $sellbillqs . "<br>";
echo "Returns Query: " . $retsellbillqs . "<br>";
echo "Expenses Query: " . $expensesqs . "<br>";

// Debug results
var_dump($allExpenses);
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [saveController.php](#) - Cash register management
- [sellbillController.php](#) - Sales operations
- [expensesController.php](#) - Expense management
- [Database Schema Documentation](#) - Table relationships

---

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