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

✅ Display cash register selection interface
✅ Generate sales and expense reports by cash register
✅ Date range filtering for time-based analysis
✅ Sales revenue aggregation across multiple bill types
✅ Expense categorization and summation
✅ 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:

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:

// 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:

// 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:

$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

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

▼

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

1Select Cash Register

- Choose from available saves

- Set date range (optional)

- Submit report request

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

▼

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

2Validate Input

- Check save ID is provided

- Verify user has access to selected save

- Process date range with time boundaries

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

▼

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

3Aggregate 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

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

▼

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

4Aggregate Expense Data

- Query expenses table with save filter

- Join with expensestype for category names

- Group by expense type

- Sum total expenses per category

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

▼

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

5Generate 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

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

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

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

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

$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

// 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:

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

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:

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

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

📚 ERP Docs

SaveReport Documentation

Save Report Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Queried via RedBeanPHP)

🔑 Key Functions

1. Default Action - Display Report Interface

2. show() - Generate Cash Register Report

🔄 Workflows

Workflow 1: Generate Cash Register Report

🌐 URL Routes & Actions

🧮 Calculation Methods

Net Sales Calculation

Expense Aggregation

Date Range Processing

🔒 Security & Permissions

User-Based Save Access

Input Validation

Data Integrity

📊 Performance Considerations

Database Optimization

Memory Management

Known Performance Issues

🐛 Common Issues & Troubleshooting

1. No Report Data

2. Incorrect Sales Totals

3. Missing Expense Categories

4. Permission Errors

🧪 Testing Scenarios

Test Case 1: Basic Report Generation

Test Case 2: Date Range Filtering

Test Case 3: Multiple Save Access

Test Case 4: Empty Result Handling

Test Case 5: Permission Restrictions

Debug Mode Enable

📚 Related Documentation