Expenses Report Controller Documentation

File: /controllers/expensesReportController.php

Purpose: Generates comprehensive expense reports with filtering by type, date, cost center, and user

Last Updated: December 20, 2024

Total Functions: 4

Lines of Code: ~346

---

📋 Overview

The Expenses Report Controller is a specialized reporting module that provides detailed expense analysis and tracking capabilities. It handles:

• • Expense reports filtered by type, date range, cost center, and user
• • Expense type-based summary reports
• • Cost center allocation reporting
• • User-based expense tracking
• • Product-linked expense analysis
• • Hierarchical expense type reporting
• • Conditional expense filtering (active/cancelled)

Primary Functions

• ✅ Generate filtered expense reports
• ✅ Expense type-based summary reporting
• ✅ Cost center allocation analysis
• ✅ Date range expense filtering
• ✅ User-specific expense tracking
• ✅ Product-linked expense reports
• ✅ Hierarchical expense type display
• ✅ Cancelled expense handling

Related Controllers

• • expensesController.php - Expense management
• • savedailyController.php - Treasury reports
• • cashflowsController.php - Cash flow analysis
• • costcenterController.php - Cost center management
• • productController.php - Product management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Comprehensive Expense Report

Location: Line 128

Purpose: Generate detailed expense report with multiple filter options

Function Signature:

// Triggered when: empty($do)
$expensestypeid = $_REQUEST['expensestypeid'];
$productid = $_REQUEST['productid'];
$from = $_REQUEST['from'];
$to = $_REQUEST['to'];
$Costcenterid = $_REQUEST['Costcenterid'];

Process Flow:

1. Load expense types hierarchy for dropdown

2. Load cost centers for filtering

3. Load YouTube tutorials

4. Process filter parameters

5. Build dynamic query string

6. Execute expense query with filters

7. Calculate totals excluding cancelled expenses

8. Display via show.html template

Dynamic Query Building:

$queryString = '';
if (isset($expensestypeid) && $expensestypeid != "-1") {
    $queryString .= " and expenses.expensestypeid = $expensestypeid ";
}
if (isset($from) && $from != "") {
    $queryString .= " and expenses.expensesdate >= '" . $from . "' ";
}
if (isset($to) && $to != "") {
    $queryString .= " and expenses.expensesdate <= '" . $to . "' ";
}
if ($productid > 0) {
    $queryString .= " and expenses.productid = $productid ";
}
if ($Costcenterid > 0) {
    $queryString .= " and expenses.costcenterid = $Costcenterid ";
}
$queryString .= " order by expensesid desc ";

Total Calculation with Conditions:

$sum = 0;
foreach ($expenseData as $data) {
    if ($data->conditions == 0) { // Only active expenses
        $sum += $data->expensesValue;
    }
}

---

2. showByType - Expense Type Summary Report

Location: Line 214

Purpose: Generate summary report grouped by expense types

Function Signature:

// Triggered when: do=showByType
$expensestypeid = (int) $_REQUEST['expensestypeid'];
$saveid = (int) $_REQUEST['saveid'];
$userid = (int) $_REQUEST['userid'];
$from = $_REQUEST['from'];
$to = $_REQUEST['to'];

Process Flow:

1. Load expense types hierarchy

2. Load cash registers and users for filtering

3. Build query string with multiple filters

4. Execute summary query grouped by expense type

5. Display via showbytype.html template

Summary Query Filters:

$queryString = '';
if (isset($expensestypeid) && $expensestypeid > 0) {
    $queryString .= " and expensestype.expensestypeid = $expensestypeid ";
}
if (isset($saveid) && $saveid > 0) {
    $queryString .= " and expenses.saveid = $saveid ";
}
if (isset($userid) && $userid > 0) {
    $queryString .= " and expenses.userid = $userid ";
}
if (isset($from) && !empty($from)) {
    $queryString .= " and expenses.expensesdate >= '" . $from . "' ";
}
if (isset($to) && !empty($to)) {
    $queryString .= " and expenses.expensesdate <= '" . $to . "' ";
}

---

3. showwithsearch() - Expense Type Filter

Location: Line 264

Purpose: Filter expenses by specific expense type

Function Signature:

function showwithsearch()

Process Flow:

1. Get expense type ID from request

2. Query expenses for specific type

3. Calculate totals

4. Assign to template

---

4. Helper Functions

show() - Show all expenses

showWithDate($from, $to) - Filter by date range

showWithDateAndType($from, $to, $expensestypeid, $productid, $Costcenterid) - Multi-filter search

---

🔄 Workflows

Workflow 1: Comprehensive Expense Report

---

Workflow 2: Expense Type Summary Report

---

🌐 URL Routes & Actions

Comprehensive Report (default):

• • expensestypeid - Expense type filter (-1 for all)
• • productid - Product filter (0 for all)
• • from - Start date (YYYY-MM-DD)
• • to - End date (YYYY-MM-DD)
• • Costcenterid - Cost center filter (0 for all)

Type Summary (do=showByType):

• • expensestypeid - Expense type filter (0 for all)
• • saveid - Cash register filter (0 for all)
• • userid - User filter (0 for all)
• • from - Start date
• • to - End date

---

🧮 Calculation Methods

Active Expense Total

$sum = 0;
foreach ($expenseData as $data) {
    if ($data->conditions == 0) { // Only active expenses
        $sum += $data->expensesValue;
    }
}

Query String Construction

$queryString = '';
if (isset($expensestypeid) && $expensestypeid != "-1") {
    $mytypedata = $ExpensetypeDAO->load($expensestypeid);
    $message .= " عرض مصروفات " . $mytypedata->expensestypename;
    $queryString .= " and expenses.expensestypeid = $expensestypeid ";
}

if (isset($from) && $from != "") {
    $message .= " من تاريخ : " . $from;
    $queryString .= " and expenses.expensesdate >= '" . $from . "' ";
}

if (isset($to) && $to != "") {
    $message .= "  الى تاريخ  " . $to;
    $queryString .= " and expenses.expensesdate <= '" . $to . "' ";
}

---

🔒 Security & Permissions

Authentication

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

Input Validation

// Type casting for numeric filters
$expensestypeid = (int) $_REQUEST['expensestypeid'];
$saveid = (int) $_REQUEST['saveid'];
$userid = (int) $_REQUEST['userid'];

// Safe parameter handling
$productid = $_REQUEST['productid'];
$from = $_REQUEST['from'];
$to = $_REQUEST['to'];

SQL Injection Prevention

• • All queries use DAO layer with parameterized statements
• • Numeric IDs cast to integer before use
• • Date strings validated in DAO layer

---

📊 Performance Considerations

Database Optimization

1. Essential Indexes:

- expenses(expensestypeid, expensesdate)

- expenses(userid, expensesdate)

- expenses(costcenterid)

- expenses(productid)

- expenses(conditions)

2. Query Efficiency:

- Single table queries with efficient WHERE clauses

- ORDER BY on primary key for consistency

- Conditional filtering reduces result sets

3. Memory Management:

- Result sets typically smaller than transaction tables

- Simple data processing without complex aggregations

Performance Tips

-- Efficient expense query structure
SELECT * FROM expenses 
WHERE conditions = 0 
AND expensestypeid = ? 
AND expensesdate BETWEEN ? AND ?
ORDER BY expensesid DESC;

-- Add compound index for best performance
CREATE INDEX idx_expenses_filter ON expenses(conditions, expensestypeid, expensesdate);

---

🐛 Common Issues & Troubleshooting

1. Incorrect Total Calculations

Issue: Totals include cancelled expenses

Cause: Missing conditions = 0 filter in calculations

Fix:

// Always check conditions in totals
foreach ($expenseData as $data) {
    if ($data->conditions == 0) { // Only active
        $sum += $data->expensesValue;
    }
}

2. Missing Expense Types

Issue: Expense type dropdown empty or incomplete

Cause: Deleted expense types or hierarchy issues

Debug:

SELECT * FROM expensestype WHERE deleted = 0 ORDER BY parent, expensestypename;

3. Date Range Issues

Issue: Date filters not working correctly

Cause: Date format mismatches

Fix: Ensure dates are in YYYY-MM-DD format

4. Cost Center Filter Problems

Issue: Cost center filter not applying

Cause: Deleted cost centers or missing assignments

Debug:

SELECT DISTINCT costcenterid FROM expenses WHERE costcenterid IS NOT NULL;
SELECT * FROM costcenter WHERE deleted = 0;

---

🧪 Testing Scenarios

Test Case 1: Basic Expense Report

1. Access controller without filters
2. Verify all expenses display
3. Check total calculation accuracy
4. Confirm expense details are complete

Test Case 2: Multi-Filter Report

1. Select expense type and date range
2. Add cost center filter
3. Verify only matching expenses appear
4. Check message displays selected filters
5. Confirm totals match filtered data

Test Case 3: Type Summary Report

1. Access showByType action
2. Apply multiple filters (user, save, dates)
3. Verify grouping by expense type works
4. Check summary totals are accurate

Test Case 4: Edge Cases

1. Test with cancelled expenses (conditions != 0)
2. Test with missing cost centers
3. Test with invalid date ranges
4. Test with non-existent expense types

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • expensesController.md - Expense management
• • savedailyController.md - Treasury reports
• • Database Schema Documentation - Table relationships

---

🔍 Key Features

Hierarchical Expense Types

• • Supports parent-child expense type relationships
• • Hierarchical display in dropdowns
• • Type-based filtering and reporting

Multi-Dimensional Filtering

• • Date Range: From/to date filtering
• • Expense Type: Category-based filtering
• • Cost Center: Department/center allocation
• • Product: Product-linked expenses
• • User: User-specific expense tracking
• • Payment Method: Cash register/save filtering

Conditional Logic

• • Active Expenses: conditions = 0
• • Cancelled Expenses: conditions != 0
• • Totals: Only include active expenses in calculations

Integration Points

• • Cash Flow Reports: Links to treasury reporting
• • Product Management: Product-linked expense tracking
• • Cost Accounting: Cost center allocation
• • User Management: User-based expense ownership

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur